﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Common;
using System.Data.SqlClient;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace DAO
{
    public class LoaiHangHoaDAO
    {
        /// <summary>
        /// Chen 1 ban ghi vao bang Loai Hang Hoa
        /// </summary>
        /// <param name="lhh">object LoaiHangHoa</param>
        public static void Insert(LoaiHangHoa lhh)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_InsertLoaiHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@sTenLoai", SqlDbType.NVarChar).Value = lhh.sTenLoai;
                sqlComm.Parameters.Add("@sHinhDaiDien", SqlDbType.NVarChar).Value = lhh.sHinhDaiDien;
                sqlComm.Parameters.Add("@sMoTa", SqlDbType.NVarChar).Value = lhh.sMoTa;
                sqlComm.Parameters.Add("@bTrangChu", SqlDbType.Bit).Value = lhh.bTrangChu;
                sqlComm.Parameters.Add("@bQuanLy", SqlDbType.Bit).Value = lhh.bQuanLy;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Sua 1 ban ghi cua bang Loai Hang Hoa
        /// </summary>
        /// <param name="lhh">object LoaiHangHoa</param>
        public static void Update(LoaiHangHoa lhh)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_UpdateLoaiHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iLoaiHangHoa_ID", SqlDbType.Int).Value = lhh.iLoaiHangHoa_ID;
                sqlComm.Parameters.Add("@sTenLoai", SqlDbType.NVarChar).Value = lhh.sTenLoai;
                sqlComm.Parameters.Add("@sHinhDaiDien", SqlDbType.NVarChar).Value = lhh.sHinhDaiDien;
                sqlComm.Parameters.Add("@sMoTa", SqlDbType.NVarChar).Value = lhh.sMoTa;
                sqlComm.Parameters.Add("@bTrangChu", SqlDbType.Bit).Value = lhh.bTrangChu;
                sqlComm.Parameters.Add("@bQuanLy", SqlDbType.Bit).Value = lhh.bQuanLy;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Xoa 1 ban ghi trong bang LoaiHangHoa
        /// </summary>
        /// <param name="id">iLoaiHangHoa_ID</param>
        public static void Delete(int id)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_DeleteLoaiHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iLoaiHangHoa_ID", SqlDbType.Int).Value = id;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lay tat ca ban ghi cua bang LoaiHangHoa
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable SelectAll()
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectAllLoaiHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(sqlComm);
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                sqlComm.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lay mot ban ghi cua bang LoaiHangHoa
        /// </summary>
        /// <param name="id">iLoaiHangHoa_ID</param>
        /// <returns>object LoaiHangHoa</returns>
        public static LoaiHangHoa SelectById(int id)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectByIdLoaiHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iLoaiHangHoa_ID", SqlDbType.Int).Value = id;
                LoaiHangHoa lhh = new LoaiHangHoa(sqlComm.ExecuteReader());
                sqlComm.Dispose();
                return lhh;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lấy các bản ghi của bảng LoaiHangHoa theo chỉ số Trang
        /// </summary>
        /// <param name="pageIndex">pageIndex</param>
        /// <param name="pageSize">pageSize</param>
        /// <param name="totalRows">totalRowsOfTable</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectAllForPaging(int pageIndex, int pageSize, ref int totalRows)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectAllLoaiHangHoaForPaging", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
                sqlComm.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                sqlComm.Parameters.Add("@totalRows", SqlDbType.Int).Value = totalRows;
                sqlComm.Parameters["@totalRows"].Direction = ParameterDirection.Output;

                SqlDataAdapter da = new SqlDataAdapter(sqlComm);
                DataTable dt = new DataTable();
                da.Fill(dt);
                totalRows = Convert.ToInt32(sqlComm.Parameters["@totalRows"].Value);
                da.Dispose();
                sqlComm.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static object SelectAllLinq()
        {
            try
            {
                DataContext dc = new DataContext(ConnectDB.GetConnect());
                Table<LoaiHangHoa> lhh = dc.GetTable<LoaiHangHoa>();
                Table<DanhMucHangHoa> dmhh = dc.GetTable<DanhMucHangHoa>();
                return dmhh.GroupBy(l => l.iLoaiHangHoa_ID).Join(lhh, d => d.Key, l => l.iLoaiHangHoa_ID, (d, l) => l);

            }
            catch (Exception ex)
            {                
                throw ex;
            }
        }
    }
}
